﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;
using System.Data.Objects;

namespace DAL
{
    public class NhanKhauDAL
    {
        //public static List<NhanKhauPublic> LoadNhanKhauBySoSHK(int SoSHK)
        //{
        //   string chuoi = "SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],[MaKS],[NgaySinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],ISNULL([MaQT],0) as MaQT,[MaCT],[MaTG],[MaTD],[MaSHK] from NhanKhau where MaSHK=" + SoSHK;
        //   var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
        //   return nhankhau.ToList();
        //}
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHK(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen],[TenGoiKhac],[CMND],ISNULL([MaKS],0) as MaKS,[NgaySinh],[NoiSinh],[GioiTinh],[QueQuan],[MaDT],[MaQH],[NgheNghiep],[MaQT],ISNULL([MaKT],0) as MaKT,[MaTG],[MaTD],[MaSHK] from NhanKhau where MaSHK=" + MaSHK;
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        //public static List<NhanKhauPublic> LoadNhanKhau()
        //{ }
        public static void InsertNhanKhau(NhanKhauPublic _nk)
        {
            Nhankhau nk = new Nhankhau();
            nk.HoTen = _nk.HoTen;
            nk.GioiTinh = _nk.GioiTinh;
            nk.NgaySinh = _nk.NgaySinh;
            nk.NoiSinh = _nk.NoiSinh;
            // nk.QuanHe = _nk.MaQH;
            nk.MaQH = _nk.MaQH; 
            nk.MaQT = _nk.MaQT;
            nk.MaDT = _nk.MaDT;
            nk.QueQuan = _nk.QueQuan;
            nk.MaSHK = _nk.MaSHK;
            nk.MaKS = null;
            nk.MaNK = _nk.MaNK;
            nk.MaKT = null;
            nk.MaTG = _nk.MaTG; 
            nk.MaSHK = nk.MaSHK;
            nk.MaTD = _nk.MaTD;
            nk.CMND = _nk.CMND;
            nk.TenGoiKhac = _nk.TenGoiKhac;
            nk.NgheNghiep = _nk.NgheNghiep;
            Common.db.AddToNhankhaus(nk);
            Common.db.SaveChanges();
        }
        public static int LoadMaNK()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaNK),0) as MaNK from NhanKhau");
            return nk.FirstOrDefault();
        }
        public static void UpdateKhaiTu(int MaKT,int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaKT=" + MaKT + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static void UpdateMaSHK(int MaSHK, int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaSHK=" + MaSHK + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static int CountNhanKhau()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null");
            return nk.ToList()[0];
        }
        public static int CountNhanKhauNam()
        {
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null and GioiTinh=1");
            return nk.ToList()[0];
        }
        public static int CountNhanKhauNu()
        {           
            var nk = Common.db.ExecuteStoreQuery<int>(@"select count(MaNK) from NhanKhau where MaKT is null and GioiTinh=0");
            return nk.ToList()[0];
        }
        public static NhanKhauPublic LoadNhanKhauByMaNK(int MaNK)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"select [MaDT],[MaQT] from NhanKhau where MaNK=" + MaNK);
            return nk.FirstOrDefault();
        }
        public static NhanKhauPublic LoadNhanKhauByCMND(string CMND)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"select * from NhanKhau where CMND=" + "'"+CMND+"'");
            return nk.FirstOrDefault();
        }
        public static void UpdateQuanHe(int MaNK,byte MaQH)
        {
            var nk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(@"update NhanKhau set MaQH=" + MaQH + "where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHKCha(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen] from NhanKhau where MaSHK=" + MaSHK+"and GioiTinh=1";
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        public static List<NhanKhauPublic> LoadNhanKhauByMaSHKMe(int MaSHK)
        {
            string chuoi = "SELECT [MaNK],[HoTen] from NhanKhau where MaSHK=" + MaSHK + "and GioiTinh=0";
            var nhankhau = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            return nhankhau.ToList();
        }
        public static int CoutSoHoKhauByYear(int firstyear, int lastyear)
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"selct count(MaSHK) from NhanKhau where year(NgayCap)>=" + firstyear + "and year(NgayCap)<=" + lastyear);
            return shk.FirstOrDefault();
        }
        
    }
}
